
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 11/01/2012 16:35:47
-- Generated from EDMX file: C:\Projects\LAVAA\src\LAVAA.DAL\LavaaModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Lavaa];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Activity_ActivityType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Activities] DROP CONSTRAINT [FK_Activity_ActivityType];
GO
IF OBJECT_ID(N'[dbo].[FK_Activity_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Activities] DROP CONSTRAINT [FK_Activity_User];
GO
IF OBJECT_ID(N'[dbo].[FK_Activity_UserReward]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Activities] DROP CONSTRAINT [FK_Activity_UserReward];
GO
IF OBJECT_ID(N'[dbo].[FK_ActivityTypeReward_ActivityType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ActivityTypeRewards] DROP CONSTRAINT [FK_ActivityTypeReward_ActivityType];
GO
IF OBJECT_ID(N'[dbo].[FK_User_UserGroup]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users] DROP CONSTRAINT [FK_User_UserGroup];
GO
IF OBJECT_ID(N'[dbo].[FK_UserReward_RewardType]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserRewards] DROP CONSTRAINT [FK_UserReward_RewardType];
GO
IF OBJECT_ID(N'[dbo].[FK_UserReward_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserRewards] DROP CONSTRAINT [FK_UserReward_User];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Activities]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Activities];
GO
IF OBJECT_ID(N'[dbo].[ActivityTypeRewards]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActivityTypeRewards];
GO
IF OBJECT_ID(N'[dbo].[ActivityTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActivityTypes];
GO
IF OBJECT_ID(N'[dbo].[RewardTypes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RewardTypes];
GO
IF OBJECT_ID(N'[dbo].[UserGroups]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserGroups];
GO
IF OBJECT_ID(N'[dbo].[UserRewards]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserRewards];
GO
IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Activities'
CREATE TABLE [dbo].[Activities] (
    [Id] uniqueidentifier  NOT NULL,
    [Date] datetime  NOT NULL,
    [DurationInMinutes] int  NOT NULL,
    [Comment] nvarchar(1000)  NULL,
    [ActivityType_Id] uniqueidentifier  NOT NULL,
    [User_Id] uniqueidentifier  NOT NULL,
    [UserReward_Id] uniqueidentifier  NULL
);
GO

-- Creating table 'ActivityTypeRewards'
CREATE TABLE [dbo].[ActivityTypeRewards] (
    [Id] uniqueidentifier  NOT NULL,
    [DurationInMinutes] int  NOT NULL,
    [NumberOfPoints] int  NOT NULL,
    [ActivityType_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'ActivityTypes'
CREATE TABLE [dbo].[ActivityTypes] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'RewardTypes'
CREATE TABLE [dbo].[RewardTypes] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [RequiredNrOfPoints] int  NOT NULL,
    [IsCurrentlyUsable] bit  NOT NULL,
    [RewardPicture] varbinary(max)  NULL,
    [Description] nvarchar(1000)  NULL
);
GO

-- Creating table 'UserGroups'
CREATE TABLE [dbo].[UserGroups] (
    [Id] uniqueidentifier  NOT NULL,
    [Name] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'UserRewards'
CREATE TABLE [dbo].[UserRewards] (
    [Id] uniqueidentifier  NOT NULL,
    [RewardedDate] datetime  NOT NULL,
    [RewardType_Id] uniqueidentifier  NOT NULL,
    [User_Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [Id] uniqueidentifier  NOT NULL,
    [UserId] nvarchar(50)  NOT NULL,
    [FirstName] nvarchar(50)  NOT NULL,
    [LastName] nvarchar(50)  NOT NULL,
    [IsAdministrator] bit  NOT NULL,
    [IsProgramResponsible] bit  NOT NULL,
    [UserGroup_Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Activities'
ALTER TABLE [dbo].[Activities]
ADD CONSTRAINT [PK_Activities]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ActivityTypeRewards'
ALTER TABLE [dbo].[ActivityTypeRewards]
ADD CONSTRAINT [PK_ActivityTypeRewards]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ActivityTypes'
ALTER TABLE [dbo].[ActivityTypes]
ADD CONSTRAINT [PK_ActivityTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'RewardTypes'
ALTER TABLE [dbo].[RewardTypes]
ADD CONSTRAINT [PK_RewardTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserGroups'
ALTER TABLE [dbo].[UserGroups]
ADD CONSTRAINT [PK_UserGroups]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'UserRewards'
ALTER TABLE [dbo].[UserRewards]
ADD CONSTRAINT [PK_UserRewards]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ActivityType_Id] in table 'Activities'
ALTER TABLE [dbo].[Activities]
ADD CONSTRAINT [FK_Activity_ActivityType]
    FOREIGN KEY ([ActivityType_Id])
    REFERENCES [dbo].[ActivityTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Activity_ActivityType'
CREATE INDEX [IX_FK_Activity_ActivityType]
ON [dbo].[Activities]
    ([ActivityType_Id]);
GO

-- Creating foreign key on [User_Id] in table 'Activities'
ALTER TABLE [dbo].[Activities]
ADD CONSTRAINT [FK_Activity_User]
    FOREIGN KEY ([User_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Activity_User'
CREATE INDEX [IX_FK_Activity_User]
ON [dbo].[Activities]
    ([User_Id]);
GO

-- Creating foreign key on [UserReward_Id] in table 'Activities'
ALTER TABLE [dbo].[Activities]
ADD CONSTRAINT [FK_Activity_UserReward]
    FOREIGN KEY ([UserReward_Id])
    REFERENCES [dbo].[UserRewards]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Activity_UserReward'
CREATE INDEX [IX_FK_Activity_UserReward]
ON [dbo].[Activities]
    ([UserReward_Id]);
GO

-- Creating foreign key on [ActivityType_Id] in table 'ActivityTypeRewards'
ALTER TABLE [dbo].[ActivityTypeRewards]
ADD CONSTRAINT [FK_ActivityTypeReward_ActivityType]
    FOREIGN KEY ([ActivityType_Id])
    REFERENCES [dbo].[ActivityTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ActivityTypeReward_ActivityType'
CREATE INDEX [IX_FK_ActivityTypeReward_ActivityType]
ON [dbo].[ActivityTypeRewards]
    ([ActivityType_Id]);
GO

-- Creating foreign key on [RewardType_Id] in table 'UserRewards'
ALTER TABLE [dbo].[UserRewards]
ADD CONSTRAINT [FK_UserReward_RewardType]
    FOREIGN KEY ([RewardType_Id])
    REFERENCES [dbo].[RewardTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserReward_RewardType'
CREATE INDEX [IX_FK_UserReward_RewardType]
ON [dbo].[UserRewards]
    ([RewardType_Id]);
GO

-- Creating foreign key on [UserGroup_Id] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [FK_User_UserGroup]
    FOREIGN KEY ([UserGroup_Id])
    REFERENCES [dbo].[UserGroups]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_User_UserGroup'
CREATE INDEX [IX_FK_User_UserGroup]
ON [dbo].[Users]
    ([UserGroup_Id]);
GO

-- Creating foreign key on [User_Id] in table 'UserRewards'
ALTER TABLE [dbo].[UserRewards]
ADD CONSTRAINT [FK_UserReward_User]
    FOREIGN KEY ([User_Id])
    REFERENCES [dbo].[Users]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserReward_User'
CREATE INDEX [IX_FK_UserReward_User]
ON [dbo].[UserRewards]
    ([User_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------